import pymysql
import datetime
import numpy as np
class dataUtils:
    def __init__(self):
        # 打开数据库连接 参数依次如下：
        self.conn = pymysql.connect(host='localhost',user='root',password='cui0411..',database='people',port=3306)
        # 使用cursor()方法获取操作游标 ,

    def getCon(self):
        con = pymysql.connect(host='localhost', user='root', password='cui0411..', database='people', port=3306)
        return con

    # 插入每日的原始数据
    def insertoriginDayRecords(self,in_num,out_num):
        day_date = datetime.datetime.now().strftime('%Y-%m-%d')
        # print(date)
        day_hour = datetime.datetime.now().hour
        print(day_hour)
        conn = self.getCon()
        cursor = conn.cursor()
        sql = 'insert into originDayRecords(in_num,out_num,day_date,day_hour) value(%s,%s,%s,%s)'
        cursor.execute(sql,(in_num , out_num ,day_date,day_hour))
        conn.commit()
        # 关闭数据库连接
        conn.close()

    # 插入每日的清洗后的数据
    def insertDayRecords(self, am_one,am_two,pm_one,pm_two,night,total):
        sql = 'insert into DayRecords(am_one,am_two,pm_one,pm_two,night,total) value(%s, %s, %s,%s, %s, %s)'
        conn = self.getCon()
        cursor = conn.cursor()
        cursor.execute(sql,(am_one ,am_two, pm_one,pm_two ,night , total ))
        conn.commit()
        # 关闭数据库连接
        conn.close()

        am_one = 10
        am_two = 20
        pm_one = 10
        pm_two = 10
        night = 10
        total = 10


    # 得到每天的人数
    def getOriginCount(self):
        conn = self.getCon()
        cursor = conn.cursor()
        sql = 'SELECT total from dayrecords where DATEDIFF(now(), date) = 0'
         # sql = 'SELECT total from dayrecords'
        cursor.execute(sql)
        results = cursor.fetchone()
        results = np.array(results)
        # print(results[0])
        conn.commit()
        # 关闭数据库连接
        conn.close()
        return results[0]
    # 插入原始数据和预测数据

    def insertPreAndOriginDate(self, today,preNum, originNum):
        sql = 'insert into preorigin(date,pre,origin) value(%s,%s,%s)'
        conn = self.getCon()
        cursor = conn.cursor()
        cursor.execute(sql, (today,preNum, originNum))
        conn.commit()
        # 关闭数据库连接
        conn.close()
    # 查询 dayrecords 的数据： 自变量 以及 每个时区的人数
    def selectDayrecordsAndTime(self,var):
        sql = 'select '+ var
        print(sql)
        sql =  sql +', weather, temp, act, festival from dayrecords'
        conn = self.getCon()
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        results = np.array(result)
        conn.commit()
        return results
        # 关闭数据库连接
        conn.close()

    # 插入预测的数据
    def insertPre(self,am_one,am_two,pm_one,pm_two,total,night):
        now_time = datetime.datetime.now()
        torrow = (now_time + datetime.timedelta(days =+ 1)).strftime("%Y-%m-%d")

        # print(date)
        day_hour = datetime.datetime.now().hour
        # print(day_hour)
        conn = self.getCon()
        cursor = conn.cursor()
        sql = 'insert into pre(date,am_one,am_two,pm_one,pm_two,total,night) value(%s,%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql,(torrow,am_one,am_two,pm_one,pm_two,total,night))
        conn.commit()
        # 关闭数据库连接
        conn.close()

    def selectXinfo(self):
        now_time = datetime.datetime.now()
        torrow = (now_time + datetime.timedelta(days =+ 1)).strftime("%Y-%m-%d")
        print((now_time + datetime.timedelta(days =+ 1)).strftime("%Y-%m-%d"))  # 获取后一天

        sql = 'SELECT weather, temp, act, festival from x where date = %s'
        var = (torrow,)
        print(sql)
        conn = self.getCon()
        cursor = conn.cursor()
        cursor.execute(sql,var)
        result = cursor.fetchall()
        results = np.array(result)
        conn.commit()
        return results
        # 关闭数据库连接
        conn.close()
    # 写成函数来测试
if __name__ == '__main__':
    datautils = dataUtils()
    day_date = datetime.datetime.now().strftime('%Y-%m-%d')
    print(day_date)
    datautils.getOriginCount()
    datautils.insertPreAndOriginDate(day_date,10,10)



